package fr.afcepf.ai85.gepeto.service;

import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import fr.afcepf.ai85.businessObj.ResultatFacture;
import fr.afcepf.ai85.businessObj.ResultatTarification;
import fr.afcepf.ai85.ent.Facture;
import fr.afcepf.ai85.ent.InscriptionPeriscolaire;
import fr.afcepf.ai85.ent.Tarifs;
import fr.afcepf.ai85.hibernate.util.HibernateUtil;

public class ServiceFacturationGeneration {

	public static void main(String[] args) {

		GenerationFactures(3, 10);	
	}

	public static List<ResultatTarification> CalculDesFactures(int idPeriode,
			int idEcole) {

		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();

		Query sqlFact1 = session
				.createSQLQuery("select e.nom_enfant, e.prenom_enfant,  ec.nom_ecole, qf.revenus, qf.nombre_parts, truncate((qf.revenus/qf.nombre_parts/12),0), "
						+ "count(p.date_inscription_irregulier) as 'nbjours', "
						+ "(select count(p.date_inscription_irregulier) "
						+ "from planning_freq_occas p, inscription_periscolaire i, eleve e "
						+ "where p.id_inscription=i.id_inscription "
						+ "and i.id_eleve=e.id_eleve "
						+ "and e.id_eleve=ie.id_eleve "
						+ "and p.id_activite=1 "
						+ "and p.id_periode=? "
						+ "and ie.identifiant_ecole=?), "
						+ "(select count(p.date_inscription_irregulier) "
						+ "from planning_freq_occas p, inscription_periscolaire i, eleve e "
						+ "where p.id_inscription=i.id_inscription "
						+ "and i.id_eleve=e.id_eleve "
						+ "and e.id_eleve=ie.id_eleve "
						+ "and p.id_activite=2 "
						+ "and p.id_periode=? "
						+ "and ie.identifiant_ecole=?), "
						+ "(select count(p.date_inscription_irregulier) "
						+ "from planning_freq_occas p, inscription_periscolaire i, eleve e "
						+ "where p.id_inscription=i.id_inscription "
						+ "and i.id_eleve=e.id_eleve "
						+ "and e.id_eleve=ie.id_eleve "
						+ "and p.id_activite=3 "
						+ "and p.id_periode=? "
						+ "and ie.identifiant_ecole=?), "
						+ " i.id_inscription "
						+ "from planning_freq_occas p, inscription_periscolaire i, "
						+ "eleve e, inscription_ecole ie, informations_qf qf, ecole ec "
						+ "where p.id_inscription=i.id_inscription "
						+ "and i.id_eleve=e.id_eleve "
						+ "and e.id_eleve=ie.id_eleve "
						+ "and e.id_eleve=qf.id_eleve "
						+ "and ec.identifiant_ecole=ie.identifiant_ecole "
						+ "and p.id_periode=? "
						+ "and ie.identifiant_ecole=? "
						+ "group by i.id_eleve order by nom_enfant;");

		sqlFact1.setInteger(0, idPeriode);
		sqlFact1.setInteger(1, idEcole);

		sqlFact1.setInteger(2, idPeriode);
		sqlFact1.setInteger(3, idEcole);

		sqlFact1.setInteger(4, idPeriode);
		sqlFact1.setInteger(5, idEcole);

		sqlFact1.setInteger(6, idPeriode);
		sqlFact1.setInteger(7, idEcole);

		List<Object[]> liste = sqlFact1.list();

		List<ResultatTarification> listeQf = new ArrayList<ResultatTarification>();
		ServiceTarifFacturationDossierMairie seTF = new ServiceTarifFacturationDossierMairie();
		for (Object[] objects : liste) {
			ResultatTarification res = new ResultatTarification();
			res.nomEnf = objects[0].toString();
			res.prenomEnf = objects[1].toString();
			res.nomEcole = objects[2].toString();
			res.revenu = (Integer) objects[3];
			res.nbPart = (Integer) objects[4];
			res.qf = objects[5].toString();
			res.nbJoursTotal = objects[6].toString();
			res.nbjMatin = objects[7].toString();
			res.nbjCant = objects[8].toString();
			res.nbjSoir = objects[9].toString();
			res.NumInscription = objects[10].toString();

			// ////////////////////////////////////////////////////////////////calcul//////////////////////////////////////////////////////////////
			double qf = Double.parseDouble(objects[5].toString());

			double nbjMatin = Double.parseDouble(res.nbjMatin);
			double nbjCant = Double.parseDouble(res.nbjCant);
			double nbjSoir = Double.parseDouble(res.nbjSoir);

			String tarifmatin;
			String tarifmidi;
			String tarifsoir;
			Tarifs tf1 = seTF.rechercherTarifsByNomActivite(1);
			Tarifs tf2 = seTF.rechercherTarifsByNomActivite(2);
			Tarifs tf3 = seTF.rechercherTarifsByNomActivite(3);
			if (qf <= tf1.getTranche1MiniBareme()) {
				tarifmatin = "0";
				tarifmidi = "0";
				tarifsoir = "0";
			} else if (qf <= tf1.getTranche2MiniBareme()) {
				tarifmatin = (tf1.getTranche1MiniTarif()).toString();
				tarifmidi = (tf2.getTranche1MiniTarif()).toString();
				tarifsoir = (tf3.getTranche1MiniTarif()).toString();
			} else if (qf <= tf1.getTranche3MiniBareme()) {
				tarifmatin = (tf1.getTranche2MiniTarif()).toString();
				tarifmidi = (tf2.getTranche2MiniTarif()).toString();
				tarifsoir = (tf3.getTranche2MiniTarif()).toString();
			} else if (qf <= tf1.getTranche4MiniBareme()) {
				tarifmatin = (tf1.getTranche3MiniTarif()).toString();
				tarifmidi = (tf2.getTranche3MiniTarif()).toString();
				tarifsoir = (tf3.getTranche3MiniTarif()).toString();
			} else if (qf <= tf1.getTranche5MiniBareme()) {
				tarifmatin = (tf1.getTranche4MiniTarif()).toString();
				tarifmidi = (tf2.getTranche4MiniTarif()).toString();
				tarifsoir = (tf3.getTranche4MiniTarif()).toString();
			} else if (qf <= tf1.getTranche6MiniBareme()) {
				tarifmatin = (tf1.getTranche5MiniTarif()).toString();
				tarifmidi = (tf2.getTranche5MiniTarif()).toString();
				tarifsoir = (tf3.getTranche5MiniTarif()).toString();
			} else if (qf < tf1.getTranche7MiniBareme()) {
				tarifmatin = (tf1.getTranche6MiniTarif()).toString();
				tarifmidi = (tf2.getTranche6MiniTarif()).toString();
				tarifsoir = (tf3.getTranche6MiniTarif()).toString();
			} else {
				tarifmatin = (tf1.getTranche7MiniTarif()).toString();
				tarifmidi = (tf2.getTranche7MiniTarif()).toString();
				tarifsoir = (tf3.getTranche7MiniTarif()).toString();
			}
			// System.out.println(tarifmatin);
			// System.out.println(tarifmidi);
			// System.out.println(tarifsoir);

			Double tarifMatinDouble = Double.parseDouble(tarifmatin);
			Double tarifCantDouble = Double.parseDouble(tarifmidi);
			Double tarifSoirDouble = Double.parseDouble(tarifsoir);

			res.montant = Math.round((nbjMatin * tarifMatinDouble)
					+ (nbjCant * tarifCantDouble) + (nbjSoir * tarifSoirDouble));

//			DecimalFormat df = new DecimalFormat ("###0.00") ; 
//				
//			String montant=df.format(montantTotal);
//			
//			res.montant=Double.parseDouble(montant);
//			
//		//	res.montant=
			
			
			
			listeQf.add(res);
		}

		for (ResultatTarification r : listeQf) {

			System.out.println(r);

		}

		return listeQf;

	}

	// //////////////////////////////////////////////////////////////////insert////////////////////////////////////////////////////////////////////
	public static void InsertDesFactures(List<ResultatTarification> listCalcul) {

		Session session = HibernateUtil.currentSession();
		Transaction tx = session.beginTransaction();

		Query hql1 = session.createQuery("FROM InscriptionPeriscolaire");

		List<InscriptionPeriscolaire> listeInscr = hql1.list();

		// for (InscriptionPeriscolaire l : listeInscr) {
		//
		// System.out.println(l.getIdInscription());
		// }

		Facture fact=null;
		try {
			for (InscriptionPeriscolaire i : listeInscr) {

				String numInscrListeCalcul = i.getIdInscription().toString();

				for (ResultatTarification r : listCalcul) {

					System.out.println(" ListInscr id " + numInscrListeCalcul+ "ListCalcul id " + r.getNumInscription());
					
					if (numInscrListeCalcul.equals(r.getNumInscription())) {
						
						System.out.println("entre dans le if");
						fact = new Facture();
						// if
						// (r.getNumInscription().equals(numInscrListeCalcul)) {
						fact.setInscriptionPeriscolaire(i);
						System.out.println("id"
								+ fact.getInscriptionPeriscolaire()
										.getIdInscription());
						fact.setDateLimitePaiement(new SimpleDateFormat(
								"yyyy-MM-dd").parse("2013-04-15"));
						fact.setDateFacture(new SimpleDateFormat("yyyy-MM-dd")
								.parse("2013-04-04"));
						Double montantInt = (double) r.getMontant();
						fact.setMontantGobal(montantInt);
						fact.setDateEnvoiFact(new SimpleDateFormat("yyyy-MM-dd")
								.parse("2013-04-04"));
						System.out.println("insert ok");
						session.save(fact);
					} else {

						System.out.println("pas d'insert");
					}

				}

			}

		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	
		System.out.println("Successfully data insert in database");
		tx.commit();
		HibernateUtil.closeSession();

	}
	
	public static List<ResultatFacture> GenerationFactures(int idPeriode,int idEcole){
		
		List<ResultatTarification> list = CalculDesFactures(idPeriode, idEcole);
		for (ResultatTarification r : list) {
			System.out.println(r.NumInscription);
		}
		InsertDesFactures(list);

		List<ResultatFacture> ListeFactGenerees = ServiceFactureHib.rechercheFactureByperiodeEcole(idPeriode, idEcole);
	
		 for (ResultatFacture l : ListeFactGenerees) {
				
				 System.out.println(l.getIdFacture());
				 }
		
		
		return ListeFactGenerees;
	
	}
	
	
}
